<?php

function list_laporan($periode_id){
	$periode_gaji_id=get_periode_gaji() ;
	if($periode_id==0){
		$periode_id=$periode_gaji_id;
	}	
	if( $periode_gaji_id==$periode_id ){
		$kalkulasi_periode=0;
	}else{
		$kalkulasi_periode=$periode_id;
	}
	$nik_start = isset($_GET['nik_start']) ? sprintf('%07d',(int)$_GET['nik_start']) :'0000001'; 	
	$nik_end = (isset($_GET['nik_end']) AND (int) $_GET['nik_end'] !='0' )? sprintf('%07d',(int)$_GET['nik_end']) :'9999999'; 	
	$end=get_batas_tmt($periode_id) ;
	$header = array(
		'No'=>array('style'=>'border-bottom:2px solid;width:5%'),  
		'NIK'=>array('style'=>'border-bottom:2px solid;width:10%'),  
		'Nama Karyawan'=>array('style'=>'border-bottom:2px solid;width:30%'),  
		'Tunjangan Kesehatan'=>array('style'=>'text-align:right;border-bottom:2px solid;width:15%'),  
		'Kacamata'=>array('style'=>'text-align:right;border-bottom:2px solid;width:10%'),  
		'Reimburse'=>array('style'=>'text-align:right;border-bottom:2px solid;width:10%'),  
		'Lain2'=>array('style'=>'text-align:right;border-bottom:2px solid;width:10%'),  
		'Total'=>array('style'=>'text-align:right;border-bottom:2px solid;width:10%'),  
	);
	$end=get_batas_tmt($periode_id) ;
	$query = "
		SELECT a.karyawan_id FROM karyawan a 
		INNER JOIN kekaryawanan b ON b.karyawan_id = a.karyawan_id 
		WHERE b.tmb  <= '{$end}' 
		AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  WHERE tmt <= '{$end}') 	
		AND ( a.nik BETWEEN {$nik_start} AND {$nik_end} ) 
		ORDER BY a.nik 
	";
	$result = my_query($query );
	$total_records = my_num_rows($result );
	$scroll_page =5;  
	$per_page =15;  
	$current_page = isset($_GET['page']) ? (int) $_GET['page'] : 1 ; 
	if($current_page < 1){
		$current_page = 1;
	}		 
	$pager_url  ="index.php?com={$_GET['com']}&periode_id={$periode_id}&nik_start={$nik_start}&nik_end={$nik_end}&task={$_GET['task']}&page=";	 
	$inactive_page_tag = 'style="padding:4px;background-color:#BBBBBB"';  
	$previous_page_text = ' Mundur '; 
	$next_page_text = ' Maju ';  
	$first_page_text = ' Awal '; 
	$last_page_text = ' Akhir ';
	
	$kgPagerOBJ = new kgPager();
	$kgPagerOBJ->pager_set(
		$pager_url, 
		$total_records, 
		$scroll_page, 
		$per_page, 
		$current_page, 
		$inactive_page_tag, 
		$previous_page_text, 
		$next_page_text, 
		$first_page_text, 
		$last_page_text ,$pager_url_last); 
	 		
	$result = my_query($query ." LIMIT ".$kgPagerOBJ->start.", ".$kgPagerOBJ->per_page); 	
	$row = array(); 
	$i = ($current_page  - 1 ) * 15; 
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$tgl_dari=$periode['dari'];
	$tgl_sampai=$periode['sampai'];
	while($ey = my_fetch_array($result )){
		$i++;
		$karyawan =  loaddata_karyawan($ey['karyawan_id']);  
		$tunjangan=get_tunjangan( $ey['karyawan_id'],$kalkulasi_periode); 				
		$biaya_kesras=get_biaya_kesra( $ey['karyawan_id'],$tgl_dari,$tgl_sampai);
		$kacamata=$biaya_kesras['kacamata'];
		$reimburse=$biaya_kesras['reimburse'];
		$lain_lain=$biaya_kesras['lain_lain'];
		$row[] = array(
			'#'=>position_text_align($i,'center'),
			'nik'=>  $karyawan['nik'] ,
			'nama'=>  $karyawan['nama_gelar'] ,
			'tunjangan'=>  position_text_align (rp_format($tunjangan) ,'right'),   
			'kacamata'=>  position_text_align (rp_format($kacamata) ,'right'),   
			'reimburse'=>  position_text_align (rp_format($reimburse) ,'right'),   
			'lain_lain'=>  position_text_align (rp_format($lain_lain) ,'right'),   
			'total'=>  position_text_align (rp_format($tunjangan+$kacamata+$reimburse+$lain_lain) ,'right'),   
		);
	}
	
	$datas = table_rows($row); 
	$paging = $kgPagerOBJ ->showPaging();
	$search = '<form method="GET"><input type="hidden" name="com" value="'.$_GET['com'].'" />
	<span style="font:10px verdana"><b>Pencarian cepat</b></span>
	<br/><input type="text"  style="width:200px;" name="key" value="'.(isset($_GET['key']) ? $_GET['key']: "").'" />
	<input class="simple_search" type="submit" value=" CARI " /> </form>';
	$view = form_header( "..." , "..."  );
	$query_check_periode = " SELECT * FROM pr_periode where periode_id <= {$periode_gaji_id} ORDER BY periode_id DESC";
	$result = my_query($query_check_periode);
	$prs = array();
	while($prow = my_fetch_array($result)){
		$prs[$prow['periode_id']] = date('m-Y',strtotime($prow['tanggal']));
	}
	$fperiode_id = array(
		'id'=>'periode_id',
		'value'=>$periode_id ,
		'name'=>'periode_id',
	);
	$view .= '<input type="hidden"  value="'.$_GET['com'].'" name="com" />';
	$view .= form_field_display( form_dropdown($fperiode_id,$prs)   , "Periode Gaji"   );	 
	$form_start = '<input type="text" size="7" value="'.$nik_start.'" name="nik_start" />';
	$form_end = '<input type="text" size="7" value="'.$nik_end.'" name="nik_end" />';
	$view .= form_field_display( $form_start.' s/d '.$form_end  , "NIK Range"   );
	$submit = array(
		'value' => (  ' Check '  ),
		'name' => 'simpan', 
		'type'=>'submit','class'=>'main_button'
	);
	$form_submit= form_dynamic($submit); 	
	$cetak = array(
		'value' => (  ' Cetak '  ),
		'onclick'=>'javascript:window.open(\'index.php?com='.$_GET['com'].'&task=cetak_jpk&periode_id='.$periode_id.'&nik_start='.$nik_start.'&nik_end='.$nik_end.'\',\'mywin\',\'left=20,top=20,width=800,height=600,toolbar=0,scrollbars,resizable=1\');',
		'type'=>'button','class'=>'main_button'
	);
	$form_cetak= form_dynamic($cetak); 	
 
	$excel = array(
		'value' => (  ' Excel '  ),
		'onclick' => 'javascript:location.href=\'index.php?com='.$_GET['com'].'&task=excel_jpk&periode_id='.$periode_id.'&nik_start='.$nik_start.'&nik_end='.$nik_end.'\'', 
		'type'=>'button','class'=>'main_button'
	);
	$form_excel= form_dynamic($excel); 
	$view .= form_field_display( $form_submit .'  '.$form_excel.'  '.$form_cetak , "&nbsp;" ,  "" );
	$view .= form_footer( );
	$view = str_replace("method=\"post\"","method=\"get\"",$view);
	return $view.table_builder($header , $datas ,  15 ,false , $paging  );
} 

function get_tunjangan( $karyawan_id,$periode_id){
	$query = "SELECT sum(a.kalkulasi) AS nominal 
		FROM pr_gaji_komponen a
		INNER JOIN pr_komponen_gaji b ON b.komponen_gaji_id=a.komponen_id 
		WHERE a.karyawan_id={$karyawan_id} 
		AND b.tunjangan_jpk='Y' 
		AND a.periode_id={$periode_id}" ;
	$result = my_query($query );
	$ey = my_fetch_array($result);
	return $ey['nominal'] ;		
}

function get_biaya_kesra( $karyawan_id,$tgl_dari,$tgl_sampai){	
	$query = "SELECT biaya_kesehatan_id,total_biaya FROM kes_biaya_kesehatan 
			WHERE karyawan_id={$karyawan_id} 
			AND (  tanggal BETWEEN '{$tgl_dari}' AND '{$tgl_sampai}' )";
	$result = my_query($query );
	$ey = my_fetch_array($result);
	$kacamata=$reimburse=$lain_lain=0 ;
	while($ey = my_fetch_array($result)){
		$t = my_get_data_by_id('kacamata','biaya_kesehatan_id',$ey['biaya_kesehatan_id']);
		if( $t ){
			$kacamata+=$ey['total_biaya'] ;
		}else{
			$t = my_get_data_by_id('penggantian_karyawan','biaya_kesehatan_id',$ey['biaya_kesehatan_id']);
			if( $t ){
				$reimburse+=$ey['total_biaya'] ;
			}else{	
				$lain_lain+=$ey['total_biaya'] ;
			}	
		}	
	}
	$datas=array();
	$datas['kacamata']=$kacamata ;
	$datas['reimburse']=$reimburse ;
	$datas['lain_lain']=$lain_lain ;
	return $datas;
}



function xl_jpk($periode_id){
	$periode_gaji_id=get_periode_gaji() ;
	if($periode_id==0){
		$periode_id=$periode_gaji_id;
	}	
	if( $periode_gaji_id==$periode_id ){
		$kalkulasi_periode=0;
	}else{
		$kalkulasi_periode=$periode_id;
	}
	$nik_start = isset($_GET['nik_start']) ? sprintf('%07d',(int)$_GET['nik_start']) :'0000001'; 	
	$nik_end = (isset($_GET['nik_end']) AND (int) $_GET['nik_end'] !='0' )? sprintf('%07d',(int)$_GET['nik_end']) :'9999999'; 	

	$header = array(
		'No'=>array('style'=>'border-bottom:2px solid;width:5%'),  
		'NIK'=>array('style'=>'border-bottom:2px solid;width:10%'),  
		'Nama Karyawan'=>array('style'=>'border-bottom:2px solid;width:30%'),  
		'Tunjangan Kesehatan'=>array('style'=>'text-align:right;border-bottom:2px solid;width:15%'),  
		'Kacamata'=>array('style'=>'text-align:right;border-bottom:2px solid;width:10%'),  
		'Reimburse'=>array('style'=>'text-align:right;border-bottom:2px solid;width:10%'),  
		'Lain2'=>array('style'=>'text-align:right;border-bottom:2px solid;width:10%'),  
		'Total'=>array('style'=>'text-align:right;border-bottom:2px solid;width:10%'),  
	);
	$end=get_batas_tmt($periode_id) ;
	$query = "
		SELECT a.karyawan_id FROM karyawan a 
		INNER JOIN kekaryawanan b ON b.karyawan_id = a.karyawan_id 
		WHERE b.tmb  <= '{$end}' 
		AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  WHERE tmt <= '{$end}') 	
		AND ( a.nik BETWEEN {$nik_start} AND {$nik_end} ) 
		ORDER BY a.nik 
	";
	$result = my_query($query );
	$row = array(); 
	$i = 0 ; 
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$tgl_dari=$periode['dari'];
	$tgl_sampai=$periode['sampai'];
	while($ey = my_fetch_array($result )){
		$i++;
		$karyawan =  loaddata_karyawan($ey['karyawan_id']);  
		$tunjangan=get_tunjangan( $ey['karyawan_id'],$kalkulasi_periode); 				
		$biaya_kesras=get_biaya_kesra( $ey['karyawan_id'],$tgl_dari,$tgl_sampai);
		$kacamata=$biaya_kesras['kacamata'];
		$reimburse=$biaya_kesras['reimburse'];
		$lain_lain=$biaya_kesras['lain_lain'];
		$row[] = array(
			'#'=>position_text_align($i,'center'),
			'nik'=>  $karyawan['nik'] ,
			'nama'=>  $karyawan['nama_gelar'] ,
			'tunjangan'=>  position_text_align (rp_format($tunjangan) ,'right'),   
			'kacamata'=>  position_text_align (rp_format($kacamata) ,'right'),   
			'reimburse'=>  position_text_align (rp_format($reimburse) ,'right'),   
			'lain_lain'=>  position_text_align (rp_format($lain_lain) ,'right'),   
			'total'=>  position_text_align (rp_format($tunjangan+$kacamata+$reimburse+$lain_lain) ,'right'),   
		);
	}
	$datas = table_rows_excel($row); 
	return table_builder_excel($header , $datas , 6 ,false ); 
}


function print_jpk($periode_id){
	$periode_gaji_id=get_periode_gaji() ;
	if($periode_id==0){
		$periode_id=$periode_gaji_id;
	}	
	if( $periode_gaji_id==$periode_id ){
		$kalkulasi_periode=0;
	}else{
		$kalkulasi_periode=$periode_id;
	}
	$nik_start = isset($_GET['nik_start']) ? sprintf('%07d',(int)$_GET['nik_start']) :'0000001'; 	
	$nik_end = (isset($_GET['nik_end']) AND (int) $_GET['nik_end'] !='0' )? sprintf('%07d',(int)$_GET['nik_end']) :'9999999'; 	
	$end=get_batas_tmt($periode_id) ;
	$query = "
		SELECT a.karyawan_id FROM karyawan a 
		INNER JOIN kekaryawanan b ON b.karyawan_id = a.karyawan_id 
		WHERE b.tmb  <= '{$end}' 
		AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  WHERE tmt <= '{$end}') 	
		AND ( a.nik BETWEEN {$nik_start} AND {$nik_end} ) 
		ORDER BY a.nik 
	";
	$result = my_query($query ); 
	$datas = array();  
	$datas[] = array(
		 'k1'=>array("NO" ,5 ,STR_PAD_LEFT),
		 'k2'=>array("NIK" ,10,STR_PAD_RIGHT),
		 'k3'=>array("Nama Karyawan" ,30,STR_PAD_RIGHT),
		 'k4'=>array("Tunjangan" ,15,STR_PAD_LEFT),  
		 'k5'=>array("Kacamata" ,10,STR_PAD_LEFT),  
		 'k6'=>array("Reimburse" ,10,STR_PAD_LEFT),  
		 'k7'=>array("Lain2" ,10,STR_PAD_LEFT),  
		 'k8'=>array("Total" ,10,STR_PAD_LEFT),  
	); 
	$datas[] = array(
		 'k1'=>array(" " ,100 ,STR_PAD_LEFT),
	); 
	$i=0;
	while($ey = my_fetch_array($result)){
		$i++;
		$karyawan =  loaddata_karyawan($ey['karyawan_id']);  
		$tunjangan=get_tunjangan( $ey['karyawan_id'],$kalkulasi_periode); 				
		$biaya_kesras=get_biaya_kesra( $ey['karyawan_id'],$tgl_dari,$tgl_sampai);
		$kacamata=$biaya_kesras['kacamata'];
		$reimburse=$biaya_kesras['reimburse'];
		$lain_lain=$biaya_kesras['lain_lain'];
		$datas[] = array(
			'k1'=>array($i ,5 ,STR_PAD_LEFT),
			'k2'=>array($karyawan['nik'] ,10,STR_PAD_RIGHT),
			'k3'=>array($karyawan['nama_gelar'] ,30,STR_PAD_RIGHT),
			'k4'=>array(rp_format($tunjangan) ,15,STR_PAD_LEFT),  
			'k5'=>array(rp_format($kacamata) ,10,STR_PAD_LEFT),  
			'k6'=>array(rp_format($reimburse) ,10,STR_PAD_LEFT),  
			'k7'=>array(rp_format($lain_lain) ,10,STR_PAD_LEFT),  
			'k8'=>array(rp_format($tunjangan+$kacamata+$reimburse+$lain_lain) ,10,STR_PAD_LEFT),  
		); 		
	}
	$view = 	str_pad("CETAK JAMINAN PEMELIHARAAN KESEHATAN",100," ",STR_PAD_BOTH);
	return $view ."\n"."\n".set_txt_report($datas);
}